package com.wd.jdbc;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.wd.base.jdbc.BaseEntity;
import com.wd.core.spring.support.SpringUtils;

public class AbstractService2 {

	public Connection getConnection() throws Exception {
		DataSource ds = (DataSource) SpringUtils.getBean("dataSource");
		return ds.getConnection();
	}

	@SuppressWarnings("rawtypes")
	public <T extends BaseEntity> List<T> select(String sql, List oList, Class<T> clazz){
		List<T> list = new ArrayList<>();

		Connection conn = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;

		try {
			conn = getConnection();// 数据库连接对象
			psmt = conn.prepareStatement(sql);

			// 给SQL语句中的？号赋值
			for (int i = 0; oList != null && i < oList.size(); i++) {
				psmt.setObject(i + 1, oList.get(i));
			}
			rs = psmt.executeQuery();// 查询结果集

			ResultSetMetaData rsmd = rs.getMetaData();// 查询结果元数据信息

			// 遍历保存数据库中对应表的列名
			List<String> columnList = new ArrayList<String>();
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				columnList.add(rsmd.getColumnName(i + 1));
			}

			// 循环遍历记录
			while (rs.next()) {// 每次获取表中对应的一条记录
				// 创建封装记录的对象
				T obj = clazz.newInstance();
				// 遍历一个记录中的所有列
				for (int i = 0; i < columnList.size(); i++) {
					// 获取列名
					String column = columnList.get(i);
					// 根据列名创建set方法
					String setMethd = "set" + column.substring(0, 1).toUpperCase() + column.substring(1);
					// 获取clazz中所有方法对应的Method对象
					Method[] ms = clazz.getMethods();

					// 循环遍历ms
					for (int j = 0; j < ms.length; j++) {
						// 获取每一个method对象
						Method m = ms[j];
						// 判断m中对应的方法名和数据库中列名创建的set方法名是否形同
						if (m.getName().equals(setMethd)) {
							// 反调set方法封装数据
							m.invoke(obj, rs.getObject(column));// 获取rs中对应的值，封装到obj中
							break; // 提高效率
						}
					}
				}
				// 封装好的一条记录对应的对象放入list集合中
				list.add(obj);
			}

			return list;
		} catch (Exception e) {
			System.err.println(e.getMessage());
		} finally {
			try {
				close(rs, psmt, conn);
			} catch (SQLException e) {
				System.err.println(e.getMessage());
			}
		}
		return null;
	}

	/**
	 * 执行增、删、改方法
	 * 
	 * @param sql
	 * @param oList
	 * @return
	 * @throws Exception
	 * @author wqj
	 */
	@SuppressWarnings("rawtypes")
	public int executeUpdate(String sql, List oList){
		int rowCount = -1;
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			
			conn = this.getConnection();
			
			ps = conn.prepareStatement(sql);
			
			if (oList != null && oList.size() > 0) {
				for (int i = 0; i < oList.size(); i++) {
					ps.setObject(i + 1, oList.get(i));
				}
			}
			
			rowCount = ps.executeUpdate();
			
			close(null, ps, conn);
			
			return rowCount;
		}catch (Exception e) {
			System.err.println(e.getMessage());
		} finally {
			try {
				close(null, ps, conn);
			} catch (SQLException e) {
				System.err.println(e.getMessage());
			}
		}
		return rowCount;
	}

	/**
	 * 关闭资源
	 * 
	 * @param rs
	 * @param ps
	 * @param conn
	 * @throws SQLException
	 * @author wqj
	 */
	public void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
		if (rs != null) {
			rs.close();
			rs = null;
		}
		if (ps != null) {
			ps.close();
			ps = null;
		}
		if (conn != null) {
			conn.close();
			conn = null;
		}
	}
}
